Databases management systems, and other data processing systems, use the memory of host computers to rapidly access data to be processed. The total memory space available on a computer host is finite. The processing of data by one component of a system competes for the finite memory of a particular host with other components of the system executing on that host. To reduce contests for the same memory space, many systems partition the memory into different areas, and devote one area for each component or group of components of the system.
For example, in some database management systems, global memory space available to the system on a host is divided into a shared global area (SGA) for holding data used by several processes, and into multiple program global areas (PGAs) for holding data used by certain corresponding processes of the system that do not share data. One PGA is allocated for each process. The SGA may contain multiple sub areas, such as a data buffer cache for input and output operations, a data dictionary cache for holding data that defines database objects, and a library cache that holds instructions used by multiple processes, such as statements, procedures and packages of procedures.
Some systems allow a system administrator to divide available memory space on a host between the different components. For example, some database management systems allow a database system administrator to divide the host memory space into an aggregate area for all the components of the SGA and an aggregate area for all the PGAs. The database system may also allow the administrator to divide one or both of these aggregate areas into sub-areas. For example, the database system administrator may set a maximum size for the data buffer cache component of SGA, or a maximum size for an individual PGA.
The sizes of the different components are based on stored values for a set of one or more memory management parameters that are retrieved when an instance of the system begins executing. The system often allows the system administrator to change the values stored for the memory management parameters. For example, a database management system allows a database system administrator to set the maximum size for the combined PGAs by setting a value for a memory management parameter called PGA_AGGREGATE_TARGET.
The performance of a system can depend on the values set for the memory management parameters. If the value of one parameter is set to a value that is too small, processes that use that area of memory may be slowed down as data is swapped between memory and some other storage medium, such as disk. If the value of the parameter is set to a value that is too large, other memory areas are made too small, and processes that use those other memory areas may be hindered.
For example, if the value of the PGA_AGGREGATE_TARGET parameter is too small, database sort operations may not have sufficient memory space to hold all the data being sorted. As a consequence, data is sorted one piece at a time. Once a piece is sorted, it is written to disk to make room for another piece. Once all pieces are sorted, they are read back from disk and merged in one or more passes based on the amount of available memory. The extra disk reads and writes substantially slow the sort operation. If the value of the PGA_AGGREGATE_TARGET parameter is too large, the SGA area may be too small to hold a buffer cache sufficiently large to hold many data blocks from disk. As a consequence, the buffer cache is more frequently flushed to disk, an operation that significantly decreases response time of the system and therefore degrades system performance.
It is often difficult for an administrator to know how to set the values for the memory management parameters. The administrator often uses some arbitrary initial values, such as evenly dividing the available memory among several components, or using default values provided by the system vendor. When system performance decreases below desirable levels, the system administrator may attempt to improve system performance by adjusting one or more of the memory management parameters. After the adjustment, the administrator may be able to detect a difference in performance. Unless the system performs the same operations on the same data, it may be difficult or impossible to tell whether the detected change in performance is due to the change in values of the memory management parameters or some other factors. Therefore, the system administrator is forced to decide to retain the new values, or to adjust the values for the memory management parameters again, based on perceived performance changes that are often ambiguously related to the changes in the values of those parameters.
Based on the foregoing description, there is a clear need for techniques to determine the effects of changes in values of system memory management parameters on system performance.
In addition, there is a need for techniques to advise a system administrator on how to change values of one or more system memory management parameters to achieve better performance.
In addition, there is a need for techniques to automatically change values of one or more system memory management parameters to achieve better performance.
The approaches described in this section could be pursued, but are not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated herein, the approaches described in this section are not admitted to be prior art merely by inclusion in this section.